1. Field of the Invention
The present invention relates to a system which accesses a database and a method therefor and, in particular, to a system which controls access to a database and a method therefor.
2. Background Art
Business application programs frequently access multiple tables, which relate to one another in a database (hereinafter referred to as a DB). For example, a program may access a table called “Customer” that manages information of customers to obtain data for a particular customer. The program then may access a table called “Account” that manages customers' accounts to obtain the account data associated with that customer. The program issues separate queries sequentially to the two tables.
In this example, the second access is made by using data retrieved with the first access as a key. That is, the data obtained with the second access depends on the data obtained with the first access. Therefore, there is a problem that these accesses cannot be merged into one by using existing optimization techniques that use data dependencies between the values of variable.
The problem is especially noticeable in batch processing in which the above business logic is repeated many times. One way to optimize batch processing may be to cache data to use in the next round of processing beforehand at each checkpoint operation. For example, values of a variable representing an access key can be analyzed to identify the range of data that will be accessed, and the data in that range can be fetched from a DB beforehand.
However, for the table “Account” mentioned above, the range of data that will be accessed cannot be identified beforehand because the access key is stored in the table “Customer”. Consequently, the data cannot be retrieved from the DB beforehand and the DB must be accessed each time in the batch iteration loop. As a result, performance of the entire program can deteriorated.
As another example, a program can access different tables using a single key. In this case, the program issues a query for each table. However, queries to DBs are often sent over an external network, and the efficiency of a program as a whole can degrade as the number of queries increases.
To address these problems, several techniques for controlling access to DBs have been proposed in the past. A brief overview of these techniques will be provided below.
(1) Generation of Inspection Loop
This technique improves the efficiency of access to table B that is made based on data obtained from table A. In this technique, an inspection loop is executed for a result set actually obtained by the query to table A during execution of a program to collect a set of keys to use for accessing table B. Then, an instruction code for obtaining data from table B in the range specified with the set of keys is generated dynamically.
(2) Use of Read-Ahead Mechanism Using CMR (Container Managed Relationship)
This method provides a mechanism that allows a programmer to specify that there are multiple tables that relate each other on an application server. The specified tables are read from the DB using a join operation and then stored in a cache that can be referred to by the program (see WebSphere Journal URL: http://websphere.syscon.com/read/43288.html and WebSphere information center URL: http://publib.boulder.ibm.com/infocenter/wasinfo/v5r0/index.jsp?topic=/com.ibm.websphere.base.doc/info/aes/ae/cejb—read.html). In particular, the programmer uses a specifier called “deployment descriptor” to specify the multiple related tables. An EJB (Enterprise Java® Beans) container that manages the cache issues SQL statements on the basis of the specifier. Java is a registered trademark.
However, in the aforementioned technique that generates the inspection loop, the processing for generating data to be inspected by issuing a query to table A is an extra work of the program. The execution of the inspection loop to analyze the access keys is also an extra of the program. These additional work can degrade the performance of the program as a whole. Furthermore, because SQL statements accessory table B are generated dynamically, static optimization cannot be applied to the SQL statements. Moreover, execution of inspection loops complicates the program and therefore can deteriorate the maintainability and robustness of the program.
In the aforementioned technique that uses the CMR, the programmer must specify mutually related tables, which task adds a burden on programmers. If the technique is to be applied to batch processing without making modifications to the structure of an existing program, the technique has limited applications. That is, in batch processing, in which the same operation is iterated, the relation between records accessed by one iteration and another iteration cannot be specified by the deployment descriptor. Therefore, it is not possible to optimize programs in such a way that a bulk of records required for a set of iterations is fetched at a time before starting the actual iterations.
Another method may be to manually rewrite SQL statements by a programmer. However, this method is not practical because the efficiency varies depending on the ability and experience of programmers, and it takes both costs and true to rewrite individual programs. Rewriting EJB (Enterprise Java® Beans) program involves extensive modifications. Specifically, in an EJB (Enterprise Java® Beans) programming model, each DB record is represented by an entity bean and is accessed with getter or setter methods. The programmer can obtain a required record by using these methods without knowing the structure of the DB. The task for manually integrating multiple DB accesses into one is against this programming model. Even if such integration were possible, the maintainability of the program would be significantly deteriorated.
Therefore, an objective of the present invention is to provide a system, method, and program capable of solving the problems described above. The objective can be achieved by a combination of features defined in the independent Claims of the present invention. The dependent Claims define more advantageous specific examples of the present invention.